<?php
// +----------------------------------------------------------------------
// | 杭州十禾信息科技有限公司
// +----------------------------------------------------------------------
// | Copyright (c) 2013-2015 http://www.shelpe.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: LeePuman <ysyzlsm@163.com>
// +----------------------------------------------------------------------
require_once APP_PATH . 'Lib/Library/Ranking.class.php';

class KeywordModel extends NewzanModel
{
    // protected $tableName = 'keywords';
    protected $tableName = null;
    
    /** @var Model 删除掉的关键词, 用来备份 */
    protected $_deletedMdl = null;
    /** @var Model */
    protected $_logMdl = null;
    /** @var RptKeywordModel */
    protected $_rptModel = null;
    
    const DEFAULT_IP_FOR_RANK = '221.176.172.86';
    
    /** keyword log 对应的操作名 */
    public $action_names = array(
    		KeywordLogActions::AUTO_CHANGE_MATCHSCOPE => '修改关键词匹配模式',
    		KeywordLogActions::AUTO_CHANGE_PRICE => '修改关键词出价',
    		KeywordLogActions::AUTO_DEL_KEYWORD => '删除关键词',
    		KeywordLogActions::AUTO_NEW_KEYWORD => '添加关键词',
    		KeywordLogActions::MANUAL_CHANGE_MATCHSCOPE => '修改关键词匹配模式',
    		KeywordLogActions::MANUAL_CHANGE_PRICE => '修改关键词价格',
    		KeywordLogActions::MANUAL_DEL_KEYWORD => '删除关键词',
    		KeywordLogActions::MANUAL_NEW_KEYWORD => '添加关键词'
    );

    protected $all_allowed_filters = array( 
                'impressions', 'click', 'cost', 'qscore','wireless_qscore',
                'sales', 'ctr', 'cpc', 'cpm', 'avgpos', 'ravgpos', 'roi', 
                'cvr', 'fav_count', 'fav_count_rate', 
                'cart_total', 'cart_total_rate', 'total_pay_count'
    );

    protected static $_SOURCE = array(
            'pc' => '1,2',
            'wireless' => '4,5',
            'summary' => '1,2,4,5'
        );

    /**
     * 初始化。主要检查session['me']。
     *
     */
    public function _initialize()
    {
        parent :: _initialize();
        $this->me = session('me');
        $this->tableName = "keywords{$this->me['user_id']}";
    }
    
    public function getRankList($keyword, $ip = '')
    {
    	if(!$ip) {
    		$ip = get_client_ip();
    		if(!$ip || substr($ip, 0, 3) == '127')
    			$ip = self::DEFAULT_IP_FOR_RANK;
    	}
    	$params = array(
    			'keyword' => $keyword,
    			'ip' => $ip,
    	);
    	$resp = $this->_api->SimbaToolsItemsTopGetRequest($params);
    	return $resp['rankeditems']->ranked_item;
    }

    //得关键词的报表信息
    public function getWordReport($campaign_id,$adgroup_id)
    {
        $the_date = date('Y-m-d');
        $params = array(
        'campaign_id' => $campaign_id,
        'adgroup_id' => $adgroup_id,
        'the_date' => $the_date
        );
        $res = $this->_api->SimbaRtrptBidwordGetRequest($params);
        $temp = (array)$res['results'];
        return (array)$temp['rt_rpt_result_entity_d_t_o'];
        
    }

    public function getCurrRank($keyword, $productId)
    {
    	$rankList = $this->getRankList($keyword);
    	$rank = 0;
    	foreach($rankList as $rankItem) {
    		++$rank;
    		if(substr($rankItem->link_url, strpos($rankItem->link_url, '=') + 1) == $productId) {
    			return $rank;
    		}
    	}
    	return 0;
    }

    public function getHistoryRank($keywordId, $record_num = 3)
    {
    	$rptKeywordMdl = $this->_getRptMdl();
    	$avgPoses = $rptKeywordMdl->where(array('keyword_id'=>$keywordId))->order('`date` DESC')->limit($record_num)->getField('avgpos', true);
    	$avgPos = array_avg($avgPoses);
    	return $avgPos;
    }
    
    public function getRank($keywordId, $keyword, $productId)
    {
    	$avgPos = $this->getHistoryRank($keywordId);
    	if($avgPos !== false) {
    		return $avgPos;
    	} else {
	    	return $this->getCurrRank($keyword, $productId);
    	}
    }
    
    public function forecastWord($keywordId, $price)
    {
    	$params = array(
    			'keyword_id' => $keywordId,
    			'bidword_price' => $price,
    	);
    	$resp = $this->_api->SimbaKeywordKeywordforecastGetRequest($params);
    	return $resp;
    }

 	public function forecastCat($adgroupId, $price)
    {
    	$params = array(
    			'adgroup_id' => $adgroupId,
    			'catmatch_price' => $price,
    	);
    	$resp = $this->_api->SimbaAdgroupCatmatchforecastGetRequest($params);
    	return $resp;
    }

    public function addKeywords($userId, $adgroupId, $keywordPrices, $apiMdl = null, $isAutodrive = false)
    {
        // 提交api
        if($apiMdl == null) 
        	$apiMdl = new ApiModel(true);
        $addedKeywords = $apiMdl->addWords($keywordPrices, $adgroupId);

        return $retKeywordsIds;
    }

    public function updateKeywords($adgroupId, $keywordPrices, $apiMdl = null, $isAutodrive = false)
    {
		if(!$keywordPrices){
			return array();
		}
    	
    	$matchscopes = array();
    	foreach($keywordPrices as $k){
    		$matchscopes[$k['keyword_id'].'i'] = $k['matchscope'];
    	}
    	// 提交API
    	if($apiMdl == null)
    		$apiMdl = new ApiModel(true);
		$updatedKeywords = $apiMdl->setPrices($keywordPrices, $adgroupId);
        if($updatedKeywords['code'] != 'success') {
            return $updatedKeywords;
        } else {
            $updatedKeywords = $updatedKeywords['result'];
        }
    	// 初始化返回值
    	$retKeywordsSuccs = array();
    	foreach($keywordPrices as $k){
    		$retKeywordsSuccs[$k['keyword_id']] = array('succ' => 0); //, 'err_msg'=>'该词已添加'
    	}

    	foreach($updatedKeywords as $k) {
            $retKeywordsSuccs[$k['keyword_id']]['succ']  = 1;
            $retKeywordsSuccs[$k['keyword_id']]['keyword_id']  = $k['keyword_id'];
            $retKeywordsSuccs[$k['keyword_id']]['price']  = $k['max_price'];
            $retKeywordsSuccs[$k['keyword_id']]['matchscope']  = $k['match_scope'];
            $retKeywordsSuccs[$k['keyword_id']]['mobile_is_default_price']  = $k['mobile_is_default_price'];
            $retKeywordsSuccs[$k['keyword_id']]['max_mobile_price']  = $k['max_mobile_price'];
    	}
    
    	return $retKeywordsSuccs;
    }

    //按当前限价更新关键词价格
    public function updateKeywordsPriceToLimit($user_id,$adgroup_id,$limit)
    {
    	$syncMdl = new SyncModel();
    	$syncMdl->syncKeywords($user_id, $adgroup_id);
    	$wordsToUpdate=array();
        $keywords = $this->where(array('adgroup_id'=>$adgroup_id))->select();
        foreach($keywords as $keyword){
        	$keywordId=$keyword['keyword_id'];
        	if($keyword['max_price']>$limit){
            	$wordsToUpdate[$keywordId] = array(
        				'word' => $keyword['word'],
        				'keyword_id' => $keywordId,
        				'max_price'  => $limit,
        				'matchscope' => $keyword['matchscope'] ? $keyword['matchscope'] : AutoDriveBaseModel::DEFAULT_MATCHSCOPE
    				);
        	}
        }
        if(count($wordsToUpdate)>0)
        	$this->updateKeywords($adgroup_id, $wordsToUpdate, null, true);
        return $wordsToUpdate;
    }
    
    /**
     * 调用直通车API从一个推广组中删除一批关键词. 同时将关键词从keywords表中删除.
     */
    public function deleteKeywords($keywordIds, $campaignId, $apiMdl = null, $isAutodrive = false)
    {
    	// 提交API
    	if($apiMdl == null)
    		$apiMdl = new ApiModel(true);
		$deletedKeywords = $apiMdl->deleteWords($keywordIds, $campaignId);    	 
    	// 初始化返回值
    	$retKeywordsSuccs = array();
    	
    	// TODO 组合Sql, 一次性删除. 同时使用transaction保证原子性.
    	foreach($deletedKeywords as $k) {
    		$this->execute('INSERT IGNORE INTO keywords_deleted (`keyword_id`, `campaign_id`, 
    			`adgroup_id`, `qscore`, `matchscope`, `nick`, `word`, `max_price`, 
    			`is_default_price`, `audit_status`, `audit_desc`, `is_garbage`, 
    			`create_time`, `modified_time`, `updated_time`, `order`, 
    			`rank_score`, `smart_price`, `wireless_qscore`,`max_mobile_price`,`mobile_is_default_price`)
    			SELECT * FROM `__TABLE__` WHERE keyword_id=' . $k['keyword_id']);
    		$this->where('keyword_id=' . $k['keyword_id'])->delete();
 
    		// 设定返回值
    		$retKeywordsSuccs[$k['word']]['succ']  = 1;
    		$retKeywordsSuccs[$k['word']]['keyword_id'] = $k['keyword_id'];
    		//     		unset($retKeywordsIds[$k['word']]['err_msg']);
    		
    		$this->addLog($k['keyword_id'], $k['word'], $k['adgroup_id'], 
    				($isAutodrive ? KeywordLogActions::AUTO_DEL_KEYWORD: KeywordLogActions::MANUAL_DEL_KEYWORD)
    		);
    	}
    	
    	return $retKeywordsSuccs;
    }

    public function deleteKeywordsFromDb($keywordIds, $campaignId)
    {
    	if(empty($keywordIds))
    		return 0;
    	
    	if(is_array($keywordIds)) {
            foreach ($keywordIds as $key => $kid) {
                if(!$kid) {
                    unset($keywordIds[$key]);
                }
            }
    		$keywordIds = join(',', $keywordIds);
            if(substr($keywordIds, -1) == ',') {
                $keywordIds = substr($keywordIds, 0, -1);
            }
    	} else {
            if(substr($keywordIds, -1) == ',') {
                $keywordIds = substr($keywordIds, 0, -1);
            }
        }
        if(strlen($keywordIds) > 0) {
            $this->execute('INSERT IGNORE INTO keywords_deleted (`keyword_id`, `campaign_id`, 
                    `adgroup_id`, `qscore`, `matchscope`, `nick`, `word`, `max_price`, 
                    `is_default_price`, `audit_status`, `audit_desc`, `is_garbage`, 
                    `create_time`, `modified_time`, `updated_time`, `order`, 
                    `rank_score`, `smart_price`, `wireless_qscore`,`max_mobile_price`,`mobile_is_default_price`)
                    SELECT * FROM `__TABLE__` WHERE keyword_id IN (' . $keywordIds . ')');
            return $this->where('keyword_id IN (' . $keywordIds . ')')->delete();
        } else {
            return false;
        }
    }
        
    public function getAdgroupKeywords($adgroup_id , $retKIdAsKey = true)
    {
        $whr = array(
            'adgroup_id' => $adgroup_id
        );
       	$local_keywords = $this->where($whr)->select(array('assoc_key'=>$retKIdAsKey));
       	return $local_keywords;
    }
    
    public function getAdgroupWordsOnly($user_id, $adgroup_id)
    {
    	$whr = array(
    			'user_id' => $user_id,
    			'adgroup_id' => $adgroup_id
    	);
    	return $this->where($whr)->getField('word', true);
    }
    
    public function getCampaignKeywords($user_id ,$campaign_id ,$params = array())
    {
        $whr = array(
           // 'user_id' => $user_id,
            'campaign_id' => $campaign_id
        );
        
        $page = $params["currentPage"];
        $pageSize = $params["pageSize"];
        
        $keywords = $this->where($whr)->page($page)->limit($pageSize)->select();
        return $keywords;
    }
    
    public function getUserKeywords( $user_id , $field = 'word,adgroup_id,max_price' , $pageNum = 1 , $pageSize = 100)
    {
        //find the campaigns first 
        $campaign_ids = D("campaigns")->where(  'user_id = ' . $user_id )->getField('campaign_id' ,true);
        if( $campaign_ids ){
            $whr['campaign_id'] = array( 'IN' , $campaign_ids );
            return $this->where($whr)->field( $field )->select();
        }else{
            return array(); 
        }
    }
    
    public function getUserKeywordNumSeries($nick , $from =null , $to =null)
    {
    	$from = $from ? $from : date("Y-m-d", strtotime("-7 day"));
    	$to = $to ? $to : date('Y-m-d',strtotime("+1 day"));
    	$sql = "SELECT count(*) AS num , 
    			AVG( max_price ) AS price FROM keywords{$this->me['user_id']} 
    			WHERE nick = '$nick'";
    	$result = $this->query($sql);
    	return array_pop( $result );
    }
    
    public function getKeywords($user_id , $keyword_ids)
    {
        $whr = array( 'user_id' => $user_id);
        if( is_array($keyword_ids) ){
            $whr["keyword_id"] = array( "in" , $keyword_ids );
        }else{
            $whr["keyword_id"] = $keyword_ids;
        }
        
        return $this->where($whr)->select();
    }
    
    public function getSpecialKeywordsStats($userId, $idField, $id, $from , $to)
    {
        if(!in_array($idField, array('adgroup_id'))) // 暂不支持 'campaign_id'
    		throw_exception('type字段无效');
    	if($id <= 0)
    		throw_exception('ID值无效');
   		if(!$from || !$to)
    		throw_exception('from, to参数中的一个或几个有误');
    	/** 类目出价已经取消，因此只有array(8) ，否则是array(8,9) */
    	return $this->_getRptMdl()->getSummaryStats($userId, 'keyword_id', array(8), 'all',
    			$from, $to, false, "$idField=" . intval($id));
    }
    
    public function getStatsBoundary( $idField , $ids , $from , $to, $sourceType = 'summary')
    {
    	$current_user = session('me');
        if(!in_array($idField, array('adgroup_id', 'campaign_id' , 'taobao_user_id'))) {
    		throw_exception('type字段无效');
    	}
    	if(!$ids) {
    		throw_exception('ID值无效');
    	}
    	if(!$from || !$to ){
    		throw_exception('from, to参数有误');
    	}
    
    	// TODO 也需要优化campaign_id, 不直接使用campaign_id查询, 先查出adgroup_ids, 然后利用 adgroup_ids进行查询.
    	if( $idField == 'taobao_user_id' ){
    		//get user nick name from the db
    		// 	    		$nick = M('users')->where(' user_id =' . $ids)->getField('nick');
    		// 	    		$whereNick = "nick = '$nick'";
    		$ids = D('Adgroup')->where(
    				array('user_id'=> (is_array($ids)? array('in', $ids) : $ids) )
    		)->getField('adgroup_id', true);
    		$idField = 'adgroup_id';
    		if(!$ids)
    			return array();
    	} elseif ($idField == 'campaign_id') {
            // $ids = D('Adgroup')->where(array('campaign_id'=> $ids))->getField('adgroup_id', true);
            if(strstr($ids, ',')) {
                //$ids = D('Adgroup')->where(array('campaign_id'=> "IN ({$ids})"))->getField('adgroup_id', true);
                $ids = D('Adgroup')->where("campaign_id IN({$ids})")->getField('adgroup_id', true);
            } else {
                $ids = D('Adgroup')->where(array('campaign_id'=> $ids))->getField('adgroup_id', true);
            }
            
            $idField = 'adgroup_id';
            if(!$ids)
                return array();
        }
    	
    	//$whrIds = $idField . '='. $ids;
    	$whrIds = is_array($ids) ? "$idField IN (" . join(',', $ids) . ")" :  "$idField = '$ids'";
    	
    	$sql = "
            SELECT keyword_id, 
            	SUM(impressions) AS impressions,SUM(click) AS click,SUM(cost)/100 AS cost,
            	SUM(direct_pay + indirect_pay)/100 AS sales,
            	SUM(direct_pay)/100 AS direct_pay,SUM(indirect_pay)/100 AS indirect_pay,
            	SUM(direct_pay_count) AS direct_pay_count,
            	SUM(indirect_pay_count) AS indirect_pay_count,
            	SUM(fav_shop_count) AS fav_shop_count,
            	SUM(fav_item_count) AS fav_item_count,
            	100*SUM(click)/SUM(impressions) AS ctr, 
            	SUM(cost)/100/SUM(click) AS cpc, 
            	10*SUM(cost)/SUM(impressions) AS cpm,
            	AVG(avgpos) AS avgpos, 
            	SUM(direct_pay + indirect_pay)/SUM(cost) AS roi,
                100*SUM(direct_pay_count + indirect_pay_count)/SUM(click) AS cvr,
                SUM(fav_shop_count + fav_item_count) AS fav_count,
            	(SUM(fav_shop_count + fav_item_count)/SUM(click))*100 AS fav_count_rate,
                SUM(cart_total) AS cart_total,
            	(SUM(cart_total)/SUM(click))*100 AS cart_total_rate,
                SUM(direct_pay_count + indirect_pay_count) AS total_pay_count
            FROM `rpt_keyword{$current_user['user_id']}`
            WHERE $whrIds AND `date` BETWEEN '$from' AND '$to' AND source IN (" . self::$_SOURCE[$sourceType] . ")
            GROUP BY keyword_id";
    	    
    	$res = $this->query($sql);
    	// get he max
    	$boundary = array_fill_keys(array(
    					"impressions",
    					"click",
    					"cost",
    					"cpc",
						//"aclick",
    					"ctr",
    					"cpm",
    					"avgpos",
    					//"price",
    					//"qscore",
    					"roi",
    					//"direct_pay",
    					//"indirect_pay",
    					"sales",
    					//"direct_pay_count",
    					//"indirect_pay_count",
    					//"fav_shop_count",
    					//"fav_item_count",
    					"cvr",
                        "fav_count",
                        "fav_count_rate",
                        "cart_total",
                        "cart_total_rate",
                        "total_pay_count"
    	           ), 0);
    
    	foreach($res as $keyword) {
    		foreach($keyword as $field => $value) {
	    		if(isset($boundary[$field])){
		    		if($boundary[$field] < $value) {
		    			$boundary[$field] = $value;
		    		}
	    		}
    		}
    	}
    	
    	
        $max_price = $this->where($whrIds)->max('max_price');
    	$max_mobile_price = $this->where($whrIds)->max('max_mobile_price');
        $boundary['price'] = $max_price ? $max_price : 0; // in cents
    	$boundary['max_mobile_price'] = $max_mobile_price ? $max_mobile_price : 0; // in cents
        $boundary['qscore'] = 10;
    	$boundary['wireless_qscore'] = 10;
    	
		return $boundary;
	}

    public function getKeywordsWithStats($idField, $ids, $from , $to , $order = 'impressions' , $desc = 'desc', 
    		$page = 1, $pageSize = 200, 
    		$filters = array(), $adgroup_name = false, $is_limit = true, $sourceType = 'summary')
    {
    	$current_user = session('me');
        if(!in_array($idField, array('adgroup_id', 'campaign_id' , 'taobao_user_id'))) {
    		throw_exception('type字段无效');
    	}
    	
    	if(!$ids) {
    		throw_exception('ID值无效');
    	}
    	if(!$from || !$to || $page <= 0 || $pageSize < 0) {
    		throw_exception('from, to, roder, desc, page, pageSize这几个参数中的一个或几个有误');
    	}
    
        // 排除 市场数据 的排序 默认使用 impressions desc
        if(in_array($order, array('q_impressions','q_cvr','q_cpc','q_roi','q_competition'))) {
            $order = 'impressions';
            $desc = 'desc';
        }

    	if($desc && !in_array(strtolower($desc), array('desc', 'asc'))) {
    		throw_exception('$desc只能为desc或asc');
    	} else if($order && !$desc) {
    		$desc = 'desc';
    	}
    		
    	// 将taobao_user_id转化为adgroup_id查询
    	if( $idField == 'taobao_user_id' ) {
    		$ids = D('Adgroup')->where(
    						array('user_id'=> (is_array($ids)? array('in', check_int_arr($ids)) : $ids) )
    				)->getField('adgroup_id', true);
    		$idField = 'adgroup_id';
    		if(!$ids)
    			return array();
    	}
        //检查数据同步性
        $rptMdl = new RptKeywordModel();
    	$whrIds = is_array($ids) ? "$idField IN ( " . join(',', $ids) . ") " :  "$idField = '$ids'";

    	$sqlOrder =  $order ? "ORDER BY $order $desc" : '';
		$sqlLimit = $is_limit ? 'LIMIT ' . (($page - 1) * $pageSize) . ",$pageSize": '';
		
		$isOnRptTbl = false;
		$propFilterSqls = '';
		if($filters) {
			$filters_on_table_keywords = array('word', 'qscore', 'max_price','matchscope','max_mobile_price','mobile_is_default_price');
			$all_allowed_filters = array('word', 'qscore', 'wireless_qscore', 'max_price', 'impressions', 'click', 'cost', 'sales',
					'direct_pay', 'indirect_pay', 'direct_pay_count', 'indirect_pay_count', 'fav_shop_count', 'fav_item_count',
					'ctr', 'cpc', 'cpm', 'avgpos', 'ravgpos', 'roi', 'cvr', 'fav_count','fav_count_rate','cart_total','cart_total_rate','total_pay_count','max_mobile_price','mobile_is_default_price');
			foreach($filters as $key => $filter) {
				if(!in_array($key, $all_allowed_filters))
					throw_exception('参数错误. 不支持该filter: ' . $key);
				if(!$isOnRptTbl && !in_array($key, $filters_on_table_keywords)) // 如果存在filter在rpt_keywords表上, 则需要使用inner join
					$isOnRptTbl = true;
				if($key != 'word' && $key != 'max_price' && $key != 'max_mobile_price') {
					// if($filter['min'] > 0) 
					// 	$propFilterSqls[$key] = "($key BETWEEN " . floatval($filter['min']) . ' AND ' . floatval($filter['max']) . ')';
					// else if(floatval($filter['max']) > 0)
					// 	$propFilterSqls[$key] = "($key IS NULL OR $key<=" . floatval($filter['max'])  . ')';
                    if(isset($filter['min']) && isset($filter['max'])) {
                        if(isset($filter['eq']) && $filter['eq']) {
                            // 包含等号
                            $propFilterSqls[$key] = floatval($filter['min']) > 0 ? "($key >= {$filter['min']} AND $key <= {$filter['max']})" : "($key IS NULL OR ($key >= {$filter['min']} AND $key <= {$filter['max']}))" ;
                        } else {
                            $propFilterSqls[$key] = "(($key > {$filter['min']} AND $key < {$filter['max']}))";
                        }
                    } else if(isset($filter['min'])) {
                        if(isset($filter['eq']) && $filter['eq']) {
                            $propFilterSqls[$key] = floatval($filter['min']) > 0 ? "($key >= {$filter['min']})" : "($key IS NULL OR $key >= {$filter['min']})" ;
                        } else {
                           $propFilterSqls[$key] = "($key > {$filter['min']})"; 
                        }
                    } else if(isset($filter['max'])) {
                        $propFilterSqls[$key] = '(';
                         $propFilterSqls[$key] .= floatval($filter['min']) > 0 ? : "$key IS NULL OR ";
                        if(isset($filter['eq']) && $filter['eq']) {
                            $propFilterSqls[$key] .= "$key <= {$filter['max']})";
                        } else {
                            $propFilterSqls[$key] .= "$key < {$filter['max']})";
                        }
                    }
				} else if($key == 'max_price' || $key == 'max_mobile_price') {
                    if(isset($filter['max']) && floatval($filter['max']) > 0) {
                        $propFilterSqls[$key] = "(k.$key BETWEEN " . floatval($filter['min']*100) . ' AND ' . floatval($filter['max']*100) . ')';
                    } else if(isset($filter['max']) && floatval($filter['max']) == 0) {

                    } else {
                        $propFilterSqls[$key] = "(k.$key IS NULL OR $key<=" . floatval($filter['max']*100)  . ')';
                    }
                }

			}
		}
		// 如果filters只在keywords表上, 使用 right join, 并将过滤条件放到 where中; 否则所有过滤条件放到 inner join中.
		$isOnRptTbl = false;
		// $propFilterSql = $propFilterSqls ? (' AND ' . join(' AND ', $propFilterSqls)) : '';
        if($filters) {
            $propFilterSql = '';
            $i = 0;
            foreach ($filters as $key => $p) {
                if($key == 'word')
                    continue;
                $u = isset($p['method']) ? $p['method'] : 'and';
                if($i === 0)
                    $u .= '(';
                $i++;
                $propFilterSql .= ' '.strtoupper($u).' ' . $propFilterSqls[$key];
            }
            $propFilterSql .= ')';
        }
		$keywordFilter = isset($filters['word']) ? " AND word LIKE '%" . mysql_escape_string($filters['word']) . "%'" : "";
		$sqlJoin = $isOnRptTbl ? " INNER JOIN `keywords{$current_user['user_id']}` k ON rk.keyword_id=k.keyword_id $keywordFilter $propFilterSql "
				: " RIGHT JOIN `keywords{$current_user['user_id']}` k ON rk.keyword_id=k.keyword_id ";
		$whrFilters = $isOnRptTbl ? '' : " $keywordFilter $propFilterSql ";
        
        // 连表 合并 推广组 标题 和 图片 和 宝贝编号
        $sqlJoin_adgroup = $adgroup_name ? " LEFT JOIN adgroups Ad ON Ad.adgroup_id = k.adgroup_id " : '';
        $sqlJoin_adgroup_field = $adgroup_name ? "Ad.title AS a_title,Ad.img_url,Ad.num_iid," : '';
        $sql = "
            SELECT 
            	{$sqlJoin_adgroup_field}k.keyword_id, k.campaign_id, k.adgroup_id, k.word, k.qscore,k.wireless_qscore, k.matchscope, k.max_price, k.is_default_price, 
            	k.audit_status, k.audit_desc, k.is_garbage, k.`order`, k.rank_score, k.smart_price, k.create_time, 
            	rk.impressions, rk.click, rk.cost, rk.sales, rk.direct_pay, 
            	rk.indirect_pay, rk.direct_pay_count, rk.indirect_pay_count, rk.fav_shop_count, rk.fav_item_count,
            	rk.ctr, rk.cpc, rk.cpm, rk.avgpos, rk.roi , rk.cvr, rk.fav_count, rk.fav_count_rate, rk.cart_total, rk.cart_total_rate, rk.total_pay_count, rkrr.ravgpos,k.max_mobile_price,k.mobile_is_default_price
            FROM 
            	(SELECT keyword_id, 
            	SUM(impressions) AS impressions,SUM(click) AS click,SUM(cost)/100 AS cost,
            	SUM(direct_pay + indirect_pay)/100 AS sales,
            	SUM(direct_pay)/100 AS direct_pay,SUM(indirect_pay)/100 AS indirect_pay,
            	SUM(direct_pay_count) AS direct_pay_count,
            	SUM(indirect_pay_count) AS indirect_pay_count,
            	SUM(fav_shop_count) AS fav_shop_count,
            	SUM(fav_item_count) AS fav_item_count,
            	100*SUM(direct_pay_count + indirect_pay_count)/SUM(click) AS cvr,
            	100*SUM(click)/SUM(impressions) AS ctr, 
            	SUM(cost)/100/SUM(click) AS cpc, 
            	10*SUM(cost)/SUM(impressions) AS cpm,
            	AVG(avgpos) AS avgpos, 
                SUM(direct_pay + indirect_pay)/SUM(cost) AS roi,
            	SUM(fav_item_count + fav_shop_count) AS fav_count,
                (SUM(fav_item_count + fav_shop_count)/SUM(click))*100 AS fav_count_rate,
                SUM(cart_total) AS cart_total,
                (SUM(cart_total)/SUM(click))*100 AS cart_total_rate,
                SUM(direct_pay_count+indirect_pay_count) AS total_pay_count
            	FROM `rpt_keyword{$current_user['user_id']}` 
            	WHERE $whrIds AND `date` BETWEEN '$from' AND '$to' AND source IN (" . self::$_SOURCE[$sourceType] . ")
            	GROUP BY keyword_id 
            	) 
            AS rk 
            $sqlJoin
            $sqlJoin_adgroup
            LEFT JOIN rpt_keyword_real_rank AS rkrr ON rkrr.keyword_id = k.keyword_id 
            WHERE ( k.$whrIds $whrFilters) 
            $sqlOrder
            $sqlLimit";
		$res = $this->query($sql);
        // echo $this->getLastSql();
        // die;
    	if($res === false) {
    		throw_exception('SQL查询出错.');
    	} else {
    		return $res;
    	}
    }

    public function getSemiautosKeywords($id, $type = 'display', $campaign_id, $adgroup_id, $unset_keyword_iids = false)
    {
        require_once LIB_PATH . "Library/TaobaoAPI/Curl.php";

        $option = $type === 'execute' ? 'exec' : 'keywords';
        $http = new Curl();

        $service = "sinbadgateway/semiautos/{$id}/{$option}";
        $params = array(
                'adgroupId' => $adgroup_id ? implode(',', $adgroup_id) : ''
            ); 
        if($campaign_id)
            $params['campaignId'] = $campaign_id;
        if($unset_keyword_iids)
            $params['keywordIds'] = $unset_keyword_iids;
        $url = build_url($service, $params);
        $res = $http->get($url);
        if($http->httpStatusCode != 200)
            return array('code' => 'error', 'msg' => '服务器错误', 'detail' => $res->message);
        return array('code' => 'success' , 'result'=> object_to_array($res));
    }

    public function deleteSemiautosKeywords($nick, $user_id, $word)
    {
       require_once LIB_PATH . "Library/TaobaoAPI/Curl.php"; 
       $http = new Curl();
       $k = 0;
       $total_word = 0;
       $successCount = 0;
       if($word) {
        foreach ($word as $campaign_id => $w) {
            $service = "sinbadservice/users/{$user_id}/campaigns/{$campaign_id}/keywords";
            $page = 1;
            $size = 100;
            $total_word += count($w);
            $total = ceil(count($w)/$size);
            for ($i=0; $i < $total; $i++,$page++) { 
                $tmp = array_slice($w, ($page-1) * $size, $size);
                $url = build_url($service);
                $http->setOpt(CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
                $resp = $http->post($url, implode(',', $tmp));
                if($http->httpStatusCode != 200) {
                    $k++;
                } 
                else {
                    $o = object_to_array($resp);
                    if(isset($o['simba_keywords_delete_response']['keywords']['keyword'])) {
                        $n = count($o['simba_keywords_delete_response']['keywords']['keyword']);
                        $successCount += $n;
                    }
                }
            }
        }
       }

       if($k > 0) {
        return array('code' => 'error', 'msg' => '执行'.$total_word.'个关键词，'.$successCount.'个关键词执行成功。');
       } else {
        return array('code' => 'success', 'msg' => '执行'.$total_word.'个关键词，'.$successCount.'个关键词执行成功。' );
       }
    }

    public function updateSemiautosKeywords($nick, $user_id, $word)
    {
        require_once LIB_PATH . "Library/TaobaoAPI/Curl.php";
        $http = new Curl();
        $service = "sinbadgateway/semiautos/exec";
        $url = build_url($service, array('userId'=>$user_id));
         $k=0;
         $n = 0;
         $successCount = 0;
         $page = 1;
         $size = 30;
         $total = ceil(count($word)/$size);
         for ($i=0; $i < $total; $i++,$page++) { 
            $tmp = array_slice($word, ($page-1) * $size, $size);
           $http->setOpt(CURLOPT_HTTPHEADER, array('Content-Type: application/json'));
            $resp = $http->post($url, json_encode($tmp));
            if($http->httpStatusCode != 200)
                $k++;
            else {
                $o = json_decode($resp, true);
                if(isset($o['simba_keywords_pricevon_set_response']['keywords']['keyword'])) {
                    $n = count($o['simba_keywords_pricevon_set_response']['keywords']['keyword']);
                    $successCount += $n;
                }     
            }
         }
         
         if($k>0)
            return array('code' => 'error', 'msg' => '执行'.count($word).'个关键词，'.$successCount.'个关键词执行成功。' );
        else{
            return array('code' => 'success', 'msg' => '执行'.count($word).'个关键词，'.$successCount.'个关键词执行成功。' );
        }
    }
    
    public function getDelWords($adgroupId, $from = '')
    {
    	if(!$from) 
    		$from = date('Y-m-d', time() - 7 * 86400);
    	return $this->_getDeletedMdl()
    			->where(array('adgroup_id'=>$adgroupId, 'deleted_time'=>array('egt', $from)))
    			->order('deleted_time DESC')
    			->select();
    }
    
    public function getDelWord($keywordId)
    {
    	return $this->_getDeletedMdl()->where(array('keyword_id'=>$keywordId))->find();
    }
    
    public function fixRank($keywordId, $rank, $keyword = '', $initTestPrice = 51, $maxPrice = 1000)
    {
    	// 从0~1元开始, 每次递增一元, 查询所需的排序所需的最低报价.
    	for($testPrice = $initTestPrice; $testPrice < $maxPrice; $testPrice += 100) {
    		$forcastRank = $this->forecastWord($keywordId, $testPrice); // $word = $forcastRank->word
    		// dbg($forcastRank);
    		if(isset($forcastRank['keyword_forecast']->price_rank)) {
    			$rankListOri = explode(',', $forcastRank['keyword_forecast']->price_rank);
    			$rankList = array();
    			foreach($rankListOri as $rank) {
    				list($k, $v) = explode(':', $rank);
    				$rankList[$k] = $v;
    			}
    			$foundPrice = array_search($rank, $rankList);
    			if($foundPrice !== false) {
    				return $foundPrice;
    			} 
    		} else {
    			return false;
    		}
    	}
    	return $maxPrice;
    }
    
    public function deleteLowQSWords($uid, $adgroupId, $qsThres = 4, $apiMdl = null, $isAutodrive = false)
    {
    	$keywords = $this->getAdgroupKeywords($adgroupId);
    	
    	if(!empty($keywords)) {
    		$firstKeyword = current($keywords);
    		$campaignId = $firstKeyword['campaign_id'];
	    	$delKeywordIds = array();
	    	foreach ($keywords as $keyword) {
	    		// $campaignId = $keyword['campaign_id'];
	    		if($keyword['qscore'] < $qsThres) {
	    			$delKeywordIds[] = $keyword['keyword_id'];
	    		}
	    	}
	    	
	    	$deleted = $this->deleteKeywords($delKeywordIds, $campaignId, $apiMdl, $isAutodrive);
	    	return count($deleted);
    	}
    	
    	return 0;
    }

	public function getTbStats($keywords, $fields = 'pv,avg_price,competition,click,ctr', $from = '', $to = '', $useApi = true)
    {
		if(!$keywords)
			return array();
		
		if(!$fields) $fields = 'pv,avg_price,competition,click,ctr,hprice';
		if(!$from) $from = date('Y-m-d', time() - 7 * 86400);
		if(!$to) $to = date('Y-m-d', time() - 86400);
		
		import('@.Library.Carine');
		$carine = new Carine();
		$result = $carine->getStatofWords($keywords, $fields, $from, $to, $useApi);
		$result = json_decode($result , true);
		if($result['status'] == 200) {
			return $result['response'];
		} else {
			throw_exception('调用服务发生错误: ' . $result['response'], 'NewzanException', $result['status']);
		}
	}
	
	public function getWordSim($word, $compWords)
    {
		import('@.Library.Carine');
		$carine = new Carine();
		return $carine->wordSim($word, $compWords);
	}
	
    public function addLog($keywordId, $keyword, $adgroupId, $action, $newVal = 0, $note = '')
    {//$oldVal = 0, 
    	return $this->_getLogMdl()->add(array(
    			'keyword_id' => $keywordId,
    			'keyword' => $keyword,
    			// 'campaign_id' => $campaignId,
    			'adgroup_id' => $adgroupId,
    			'action' => $action,
    			// 'old_int_val' => $oldIntVal,
    			'new_int_val' => $newVal,
    			'note' => $note,
    	));
    }
    
    public function syncQscore($adgroupId)
    {
    	$maxTry = 3;
    	$try = 0;
    	
    	while(++$try <= $maxTry) {
    		$updatedNum = 0;
	    	
	    	$resp = $this->_api->SimbaKeywordscatQscoreGetRequest(array('adgroup_id' => $adgroupId));
	    	$resp = object_to_array($resp);
	    	
	    	if(isset($resp['qscore']['keyword_qscore_list']['keyword_qscore'])) {
	    		$wordScores = $resp['qscore']['keyword_qscore_list']['keyword_qscore'];
	    		foreach($wordScores as $wordScore) {
	    			if(isset($wordScore['qscore']) && $wordScore['qscore'] > 0) {
		    			$this->where(array('keyword_id' => $wordScore['keyword_id'], 'adgroup_id' => $adgroupId))
		    					->save(array('qscore' => $wordScore['qscore']));
	    				++$updatedNum;
	    			}
	    		}
	    		// 超过3/4的词已经有质量得分了或者达到最大尝试次数
	    		if($updatedNum >= 0.75 * count($wordScores) || $try == $maxTry) 
	    			return $updatedNum;
	    		else // 否则2s后再来一次
	    			sleep(2);
	    	} else {
	    		return 0;
	    	}
    	}
    }
    
    public function readLog($log, $simple_disp)
    {
    	$log = parent::readLog($log, $simple_disp);
    	$dispWord = $simple_disp ? '' : '"' . $log['keyword'] . '"';
    	switch($log['action']){
    		case KeywordLogActions::AUTO_CHANGE_MATCHSCOPE:
    		case KeywordLogActions::MANUAL_CHANGE_MATCHSCOPE:
    			$matchscopes = array(
    				1 => '精确匹配',
    				2 => '子串匹配',
    				4 => '广泛匹配'	
    			);
    			$log['action_note'] = '修改' . $dispWord . '匹配模式为' . $matchscopes[$log['action_value']];
    		break;
    		
    		case KeywordLogActions::AUTO_CHANGE_PRICE:
    		case KeywordLogActions::MANUAL_CHANGE_PRICE:
    			// $note = $log['action_note'];
    			$new_price = $log['action_value'] / 100.0; // isset($note['max_price']) ? $note['max_price'] / 100.0 : $log['action_value'] / 100.0;
    			$log['action_note'] = '修改' . $dispWord . '价格为￥' . $new_price;
    		break;
    	
    		case KeywordLogActions::AUTO_NEW_KEYWORD:
    		case KeywordLogActions::MANUAL_NEW_KEYWORD:
    			// $note = $log['action_note'];
    			$new_price = $log['action_value'] / 100.0; // isset($note['max_price']) ? $note['max_price'] / 100.0 : $log['action_value'] / 100.0;
    			$log['action_note'] = '添加关键词' . $dispWord . '. 价格为￥' . $new_price;
    		break;
    			
    		case KeywordLogActions::AUTO_DEL_KEYWORD:
    		case KeywordLogActions::MANUAL_DEL_KEYWORD:
    			// $note = $log['action_note'];
    			$log['action_note'] = '删除关键词'. $dispWord;
    		break;
    	}
    	return $log;
    }
    
    /**
     * @return Model
     */
    protected function _getDeletedMdl()
    {
    	if($this->_deletedMdl == null) {
    		$this->_deletedMdl = M('keywords_deleted');
    	}	
    	return $this->_deletedMdl;
    }
    
    /**
     * @return Model
     */
    protected function _getLogMdl()
    {
    	if($this->_logMdl == null) {
    		$this->_logMdl = M('keywords_log');
    	}
    	return $this->_logMdl;
    }

    /**
     * @return RptKeywordModel
     */
    protected function _getRptMdl()
    {
    	if($this->_rptModel == null) {
    		$this->_rptModel = D('RptKeyword');
    	}
    	return $this->_rptModel;
    }

    /**
     * 查询无展现词
     * @return [type] [description]
     */
    public function noShowkeyword($user_id, $campaign_iids, $from, $to)
    {
        $resQuery = array(
                'total_items' => 0
            );
        if(is_array($campaign_iids))
            $campaign_iids = implode(',', $campaign_iids);
        $where[] = "user_id = {$user_id}";
        $where[] = "campaign_id in({$campaign_iids})";
        $where[] = "online_status = 'online'";
        $adgroups = M('adgroups')->field('adgroup_id')->where(implode(' AND ', $where))->select(array('assoc_key' => true));
        $adgroup_iids = implode(',', array_keys($adgroups));
        unset($adgroups);
        $sql = "SELECT 
                    k.keyword_id, IFNULL(rpt.impressions, 0) AS impressions, k.word,
                    k.max_price,k.matchscope,k.create_time,
                    k.campaign_id,k.adgroup_id
                FROM
                (
                    SELECT
                        keyword_id, SUM(impressions) AS impressions
                    FROM
                        `rpt_keyword{$user_id}`
                    WHERE
                        adgroup_id in({$adgroup_iids}) AND date BETWEEN '{$from}' AND '{$to}'
                    GROUP BY 
                        keyword_id
                ) AS rpt
                RIGHT JOIN `keywords{$user_id}` AS k ON k.keyword_id = rpt.keyword_id
                WHERE 
                    adgroup_id in({$adgroup_iids}) AND impressions IS NULL";
        $res = $this->query($sql);
        // echo $this->getLastSql();
        // die;
        if($res === false)
            throw_exception('SQL查询出错.');
        else {
            $resQuery['total_items'] = count($res);
            if($resQuery['total_items'] > 0)
                $resQuery['items'] = $res;
        }
        return $resQuery;
    }
}


/**
 * 对直通车关键词进行的各项操作常量
 *
 * @author weiping
 */
class KeywordLogActions {
	/** @var int 自动加词. */
	const AUTO_NEW_KEYWORD = 0;
	/** @var int 自动改价. */
	const AUTO_CHANGE_PRICE = 1;
	/** @var int 自动删词. */
	const AUTO_DEL_KEYWORD = 2;
	/** @var int 自动修改匹配方式. */
	const AUTO_CHANGE_MATCHSCOPE = 3;
	/** @var int 手动加词. */
	const MANUAL_NEW_KEYWORD = 10;
	/** @var int 手动改价. */
	const MANUAL_CHANGE_PRICE = 11;
	/** @var int 手动删词. */
	const MANUAL_DEL_KEYWORD = 12;
	/** @var int 手动修改匹配方式. */
	const MANUAL_CHANGE_MATCHSCOPE = 13;

}
/**
 * auto_maintain_log表中的action字段的可取值
 * @author weiping
 */
class AutoMaintainLogActions {
	const NO_CHANGE = 0;
	const INC_PRICE = 1;
	const DEC_PRICE = 2;
	const DEL_WORD = 3;
	const INC_PRICE_NOT_FULL = 4;
	const DEC_PRICE_NOT_FULL = 5;
	const DEC_PRICE_TO_MAX_ALLOWED = 6;
}
